package cn.jll.dev.utils.db;

import cn.jll.dev.utils.db.exception.DataBaseTypeException;
import cn.jll.dev.utils.db.pojo.DataBaseLink;
import cn.jll.dev.utils.db.pojo.DataBaseType;
import cn.jll.dev.utils.db.api.RowMapper;

import java.sql.*;
import java.util.*;

/**
 * @description: 数据库工具类
 * @author: jinleilei
 * @create: 2019-05-29 15:43:26
 * @Version: v1.0
 **/
public class DataBaseUtil {


    /**
     * 数据库连接配置对象
     */
    private DataBaseLink dataBaseLink;

    /**
     * 数据库连接
     */
    private Connection connection;

    public DataBaseLink getDataBaseLink() {
        return dataBaseLink;
    }

    public Connection getConnection() {
        return connection;
    }

    public DataBaseUtil(DataBaseLink dataBaseLink) throws SQLException, ClassNotFoundException {
        this.dataBaseLink = dataBaseLink;
        initDataConnection(dataBaseLink);
    }

    private DataBaseType getType(DataBaseLink db) {
        return DataBaseType.getByName(db.getType());
    }

    private void initDataConnection(DataBaseLink db) throws ClassNotFoundException, SQLException {
        String name = "";
        String url = "";
        switch (getType(db)) {
            case MYSQL:
                name = "com.mysql.cj.jdbc.Driver";
                url = "jdbc:mysql://" + db.getLink() + ":" + db.getPort() + "/" + db.getDatabaseName() + "?useSSL=false&&zeroDateTimeBehavior=convertToNull";
                break;
            case Oracle:
                name = "oracle.jdbc.driver.OracleDriver";
                url = "jdbc:oracle:thin:@" + db.getLink() + ":" + db.getPort() + ":" + db.getDatabaseName();
                break;
            case SqlServer:
                name = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                url = "jdbc:sqlserver://" + db.getLink() + ":" + db.getPort() + ";DatabaseName=" + db.getDatabaseName();
                break;
            default:
                throw new DataBaseTypeException();
        }
        Class.forName(name);
        connection = DriverManager.getConnection(url, db.getUsername(), db.getPassword());
    }


    /**
     * 查询单个对象
     *
     * @param sql
     * @param mapper
     * @param args
     * @param <T>
     * @return
     */
    public <T> T queryForObject(String sql, RowMapper<T> mapper, Object... args) {
        T result = null;
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            setQueryStatement(statement, args);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                result = mapper.mapRow(rs, 0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closePreparedStatement(statement);
        }
        return result;
    }

    /**
     * 查询列表
     *
     * @param sql
     * @param mapper
     * @param <T>
     * @return
     */
    public <T> List<T> queryForList(String sql, RowMapper<T> mapper) {
        List<T> result = new ArrayList<>();
        PreparedStatement statement = null;

        try {
            statement = connection.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            int index = 0;
            while (rs.next()) {
                result.add(mapper.mapRow(rs, index++));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closePreparedStatement(statement);
        }
        return result;
    }


    /**
     * 查询列表
     *
     * @param sql
     * @param args
     * @return 全部数据的 Map对象
     */
    public List<Map<String, Object>> queryForList(String sql, Object... args) {
        List<Map<String, Object>> result = new ArrayList<>();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            setQueryStatement(statement, args);
            ResultSet rs = statement.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rowData = new LinkedHashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnLabel(i), rs.getObject(i));
                }
                result.add(rowData);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closePreparedStatement(statement);
        }
        return result;
    }

    private void setQueryStatement(PreparedStatement statement, Object... args) {
        try {
            int index = 1;
            for (Object o : args) {
                statement.setObject(index++, o);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void closePreparedStatement(PreparedStatement preparedStatement) {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
